using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using MvcExtExample.Models;

namespace MvcExtExample.Controllers
{
    public class WellManageController : Controller
    {
        private string Well_ID;
        private string Well_Type_ID;
        private string Dep_ID;

        public ActionResult addWellOperation(string WellTypeSelect, string WorkZoneSelect, string OilFieldSelect, string SSDDSelect, string SSXDSelect)
        {
            bool result = addWellDAL(WellTypeSelect, WorkZoneSelect, OilFieldSelect, SSDDSelect, SSXDSelect);
            if (result)
                return Content("添加成功！");
            else
                return Content("添加失败！");
        }

        private bool addWellDAL(string WellTypeSelect, string WorkZoneSelect, string OilFieldSelect, string SSDDSelect, string SSXDSelect)
        {
            Random rd = new Random();
            string temp = rd.Next().ToString();
            Well_ID = temp;
            bool result = false;
            //连接数据库
            SqlConnection ts = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            //打开数据库连接
            ts.Open();
            //初始化查询语句
            SqlCommand it = new SqlCommand("INSERT INTO [dbo].[Tb_CommonWell]( [Well_ID] ,[Well_Type_ID] ,[Dep_ID] ,[Status])VALUES  ( '" + Well_ID + "' ,'" + Well_Type_ID + "' ,'" + Dep_ID + "' ,'有效" + "')", ts);
            //对SQL Server数据库执行一个transact-SQL语句
            int rows = it.ExecuteNonQuery();
            if (rows > 0)
                result = true;
            //关闭数据库连接
            ts.Close();
            return result;
        }

        //
        // GET: /addWell/
        public ActionResult addWell()
        {
            return View();
        }

        //
        // GET: /manageWell/
        public ActionResult manageWell()
        {
            return View();
        }
        //
        // GET: /Back/

        public ActionResult Index()
        {
            return View();
        }

        //
        // GET: /Back/Details/5

        public ActionResult Details(int id)
        {
            return View();
        }

        //
        // GET: /Back/Create

        public ActionResult Create()
        {
            return View();
        }

        //
        // POST: /Back/Create

        [HttpPost]
        public ActionResult Create(FormCollection collection)
        {
            try
            {
                // TODO: Add insert logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Back/Edit/5

        public ActionResult Edit(int id)
        {
            return View();
        }

        //
        // POST: /Back/Edit/5

        [HttpPost]
        public ActionResult Edit(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add update logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Back/Delete/5

        public ActionResult Delete(int id)
        {
            return View();
        }

        //
        // POST: /Back/Delete/5

        [HttpPost]
        public ActionResult Delete(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add delete logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        
        [AcceptVerbs(HttpVerbs.Get)]
        public string WellsQuery()
        {
            //List<CommonWell> litestModel = new List<CommonWell>();
            //framework.JSONHelper jsonHelp = new framework.JSONHelper();
            //int TotalRecords = 0;
            //string strSqlStr = "SELECT TOP 18 [Well_ID],[Well_Type_ID],[Dep_ID],[Status] FROM [Oil_Water_System].[dbo].[Tb_CommonWell]";
            //framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            //DataSet DSet = cs.GetDataSet(strSqlStr);
            //if (DSet != null)
            //{
            //    DataTable DTable = DSet.Tables[0];
            //    for (int i = 0; i < DTable.Rows.Count; i++)
            //    {
            //        DataRowView rowview = DSet.Tables[0].DefaultView[i];
            //        litestModel.Add(new CommonWell()
            //        {
            //            active = true,//默认为勾选状态
            //            Well_ID = rowview["Well_ID"].ToString(),
            //            Well_Type_ID = rowview["Well_Type_ID"].ToString(),
            //            Dep_ID = rowview["Dep_ID"].ToString(),
            //            Status = rowview["Status"].ToString()
            //        });
            //    }
            //    return Json(litestModel, JsonRequestBehavior.AllowGet);
            //}
            //else
            //{
            //    return null;
            //}

            int start = 0;
            int.TryParse(Request.Params["start"], out start);
            framework.JSONHelper jsonHelp = new framework.JSONHelper();
            int TotalRecords = 0;
            string total = "SELECT COUNT(*) FROM [dbo].[Tb_CommonWell]";
            string strSqlStr = "SELECT TOP 18 * FROM [Tb_CommonWell] WHERE Well_ID NOT IN ( SELECT TOP " + start + " Well_ID FROM [Tb_CommonWell]   ORDER BY Well_ID ) ORDER BY Well_ID";
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            TotalRecords = cs.ReturnSQL(total);
            DataSet DSet = cs.GetDataSet(strSqlStr);
            if (DSet != null)
            {
                DataTable dt = DSet.Tables[0];
                StringBuilder JsonString = new StringBuilder();
                if (dt != null && dt.Rows.Count > 0)
                {
                    JsonString.Append("{\"total\":" + TotalRecords + ",\"data\":[ ");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        JsonString.Append("{ ");
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (j < dt.Columns.Count - 1)
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\",");
                            }
                            else if (j == dt.Columns.Count - 1)
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"");
                            }
                        }

                        if (i == dt.Rows.Count - 1)
                        {
                            JsonString.Append("} ");
                        }
                        else
                        {
                            JsonString.Append("}, ");
                        }
                    }
                    JsonString.Append("]}");
                    return JsonString.ToString();
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }

        }

    }
}
